﻿CREATE PROCEDURE reports.s_ChargeOffs_Brad AS
DECLARE @Pattern TABLE(ID int,Element sysname)
INSERT INTO @Pattern
SELECT ID,Element FROM dbo.Split('Insurance\s+Claim,Abandon,Legal,Impound,Secured\s+Unit,Reservation',',')

--TRUNCATE TABLE reports.t_ChargeOffs
--INSERT INTO reports.t_ChargeOffs
SELECT T.*
INTO reports.t_ChargeOffs
FROM
(
SELECT DISTINCT C.[Id #], R.CompanyID,C.Remark Note,P.ID PatternID
FROM         usix.t_RepoFile AS R INNER JOIN
                      usix.t_Comment AS C ON R.[Cust ID#] = C.[Id #] AND R.CompanyID = C.CompanyID
INNER JOIN @Pattern P
	ON (dbo.clr_RegEx_Match(C.Remark, P.Element, 1) > '')
WHERE     (R.Chargeoff > 5000) AND (R.[Repo Date] >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102))
AND C.CompanyID = 1
UNION
SELECT DISTINCT CF_E.[Customer #], CF_E.CompanyID_Raw,N.Note,P.ID PatternID
FROM         usix.t_CustomerFile_Export AS CF_E INNER JOIN
                      t_Note AS N ON CF_E.LoanID = N.LoanID
INNER JOIN @Pattern P
	ON (dbo.clr_RegEx_Match(N.Note, P.Element, 1) > '')
WHERE     (CF_E.[Repo Date] >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND (CF_E.Chargeoff > 5000)
AND CF_E.CompanyID_Raw = 1
)T LEFT OUTER JOIN reports.t_ChargeOffs_SIF SIF ON SIF.[Id #] = T.[Id #] AND SIF.CompanyID = T.CompanyID
WHERE SIF.[Id #] IS NULL

--SELECT [Id #],CompanyID
--INTO reports.t_ChargeOffs_SIF
--FROM
--(
--SELECT DISTINCT    T.[Id #], T.CompanyID
--FROM         (SELECT usix.t_Comment.[Id #], usix.t_RepoFile.CompanyID
--                       FROM          usix.t_RepoFile INNER JOIN
--                                              usix.t_Comment ON usix.t_RepoFile.[Cust ID#] = usix.t_Comment.[Id #] AND 
--                                              usix.t_RepoFile.CompanyID = usix.t_Comment.CompanyID
--                       WHERE      (usix.t_RepoFile.Chargeoff > 5000) AND (usix.t_RepoFile.[Repo Date] >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND 
--                                              (dbo.clr_RegEx_Match(usix.t_Comment.Remark, N'(Settelment|Settlement)', 1) > '') AND usix.t_Comment.CompanyID = 1)
-- AS T INNER JOIN
--                      usix.t_Comment AS t_Comment_1 ON T.[Id #] = t_Comment_1.[Id #] AND T.CompanyID = t_Comment_1.CompanyID
--WHERE (dbo.clr_RegEx_Match(t_Comment_1.Remark, N'SIF', 1) > '')
--UNION
--SELECT CF_E.[Customer #], CF_E.CompanyID_Raw
--FROM         usix.t_CustomerFile_Export AS CF_E INNER JOIN
--                      t_Note AS N ON CF_E.LoanID = N.LoanID
--WHERE     (CF_E.[Repo Date] >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND (CF_E.Chargeoff > 5000)
--AND CF_E.CompanyID_Raw = 1 AND
--dbo.clr_RegEx_Match(N.Note, '(Settelment|Settlement)[\s\S]+SIF', 1) > ''
--)T

SELECT [Id #],CONVERT(bit,[1]) Insurance,CONVERT(bit,[2]) Abandon,CONVERT(bit,[3]) Legal,
	CONVERT(bit,[4]) Impound,CONVERT(bit,[5]) Secured,CONVERT(bit,[6]) Reservation
FROM
(SELECT [Id #],PatternID FROM reports.t_ChargeOffs CO WITH(nolock))P
PIVOT(
	COUNT(PatternID)
FOR PatternID IN ([1],[2],[3],[4],[5],[6])
)PVT
